package xyz.hlh.boot2.poitest.usermodel;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFFormulaEvaluator;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author HLH
 * @description POI 大文件读
 * @email 17703595860@163.com
 * @date : Created in  2021/8/2 下午9:33
 */
public class POIBigRead {

    private static final String PATH = "/media/hlh/13B69828E0E35204/A-IdeaProject-UOS/spring-boot-csdn/02-poi-easyExcel/src/file/";


    /**
     * xls读
     * 使用 HSSFWorkbook 进行操作
     * 读取 65536行数据 占用内存 2G左右，用时 30.602s
     */
    @Test
    public void poi03Read() {
        long start = System.currentTimeMillis();

        List<List<Object>> list = new ArrayList<>();
        // 工作簿
        try (Workbook workbook = new HSSFWorkbook(new FileInputStream(PATH + "03big.xls"))) {
            // 工作表
            Sheet sheet1 = workbook.getSheetAt(0);
            int lastRowNum = sheet1.getLastRowNum();
            if (lastRowNum == 0) {
                return;
            }
            Iterator<Row> iterator = sheet1.iterator();
            while (iterator.hasNext()) {
                ArrayList<Object> rowData = new ArrayList<>();

                Row row = iterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    Object cellValue = getCellData(cell, workbook);
                    rowData.add(cellValue);
                }
                list.add(rowData);
            }
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }

        long end = System.currentTimeMillis();
        System.out.println();
        System.out.println(((double) end - start) / 1000);
    }

    /**
     * xlsx 读
     * 使用 XSSFWorkbook 进行操作
     * 读取30万条数据，直接OOM
     */
    @Test
    public void poi07Read() {
        long start = System.currentTimeMillis();

        List<List<Object>> list = new ArrayList<>();
        // 工作簿
        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(PATH + "07big.xlsx"))) {
            // 工作表
            Sheet sheet1 = workbook.getSheetAt(0);
            // 最后的行号 真实行号-1，标识下标
            int lastRowNum = sheet1.getLastRowNum();
            // 物理行数，真实行数
            // int physicalNumberOfRows = sheet1.getPhysicalNumberOfRows();
            if (lastRowNum == 0) {
                return;
            }
            for (int i = 0; i <= lastRowNum; i++) {
                ArrayList<Object> rowData = new ArrayList<>();

                Row row = sheet1.getRow(i);
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    Object cellValue = getCellData(cell, workbook);
                    rowData.add(cellValue);
                }
                list.add(rowData);
            }
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }

        long end = System.currentTimeMillis();
        System.out.println();
        System.out.println(((double) end - start) / 1000);
    }

    /**
     * 解析列
     * @param cell 单元格
     * @param workbook 工作簿
     * @return 解析完的数据
     */
    private Object getCellData(Cell cell, Workbook workbook) {
        Object result = null;

        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case STRING:
                result = cell.getStringCellValue();
                break;
            case BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    result = cell.getDateCellValue();
                } else {
                    result = cell.getNumericCellValue();
                }
                break;
            case FORMULA:
                FormulaEvaluator formulaEvaluator = null;
                if (workbook instanceof HSSFWorkbook) {
                    formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                } else if (workbook instanceof XSSFWorkbook) {
                    formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                } else if (workbook instanceof SXSSFWorkbook) {
                    formulaEvaluator = new SXSSFFormulaEvaluator((SXSSFWorkbook) workbook);
                }
                if (formulaEvaluator != null) {
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    result = getCellData(evaluate, workbook);
                }
                break;
            default:
                break;
        }
        return result;
    }


    /**
     * 获取CellValue的值
     * @param cellValue CellValue
     * @param workbook 工作薄
     * @return 解析完的数据
     */
    private Object getCellData(CellValue cellValue, Workbook workbook) {
        Object result = null;

        CellType cellType = cellValue.getCellTypeEnum();
        switch (cellType) {
            case STRING:
                result = cellValue.getStringValue();
                break;
            case BOOLEAN:
                result = cellValue.getBooleanValue();
                break;
            case NUMERIC:
                result = cellValue.getNumberValue();
                break;
            default:
                break;
        }
        return result;
    }

}
